package chen.web.blog;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.Date;

import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

import chen.web.DataSourceProvider;
import chen.web.user.Account;
import chen.web.user.AccountManager;

public class BlogManagerImpl implements BlogManager {

	private static SimpleJdbcTemplate simpleJdbcTemplate = new SimpleJdbcTemplate(DataSourceProvider.getDataSource());
	private static JdbcTemplate  jdbcTemplate  = new JdbcTemplate(DataSourceProvider.getDataSource());	
	
	private AccountManager am;
	public BlogManagerImpl(AccountManager am){
		this.am = am;
	}
	
	private static final String SQL_33 = "insert into web_blog(blog_name,blog_description,blog_ownerId, blog_created) values(?,?,?,?)";
	@Override
	public Blog create(final Blog blog) {
		// TODO Auto-generated method stub
		blog.setCreated(new Date());
		KeyHolder keyHolder = new GeneratedKeyHolder();
		jdbcTemplate.update(new PreparedStatementCreator(){
			@Override
			public PreparedStatement createPreparedStatement(Connection conn)
					throws SQLException {
				// TODO Auto-generated method stub
				PreparedStatement ps = conn.prepareStatement(SQL_33, PreparedStatement.RETURN_GENERATED_KEYS);
				ps.setString(1, blog.getName());
				ps.setString(2, blog.getDescription());
				ps.setInt(3, blog.getOwner().getId());
				ps.setTimestamp(4, new Timestamp(blog.getCreated().getTime()));
				return ps;	
			}	
		}, keyHolder);
		
		int id = keyHolder.getKey().intValue();
		blog.setId(id);
		return blog;
	}

	private static final String SQL_34 = "update web_blog set blog_name=?,blog_description=? where blog_id=?";
	@Override
	public void update(Blog blog) {
		// TODO Auto-generated method stub
		simpleJdbcTemplate.update(SQL_34, blog.getName(), blog.getDescription(), blog.getId());
	}

	private static final String SQL_35 = "select * from web_blog where blog_id=?";
	@Override
	public Blog getBlog(int id) {
		// TODO Auto-generated method stub
		Blog blog = null;
		try{
			blog = simpleJdbcTemplate.queryForObject(SQL_35, new BlogRowMapper(), id);
		} catch(EmptyResultDataAccessException e){
			
		}
		if(blog != null){
			blog.setOwner(am.getAccount(blog.getOwner().getId()));	
		}
		return blog;
	}
	
	private static final String SQL_36 = "select * from web_blog where blog_ownerId=?";
	@Override
	public Blog getBlog(Account account) {
		// TODO Auto-generated method stub
		Blog blog = null;
		try{
			blog = simpleJdbcTemplate.queryForObject(SQL_36, new BlogRowMapper(), account.getId());
		} catch(EmptyResultDataAccessException e){
			
		}
		if(blog != null){
			blog.setOwner(am.getAccount(blog.getOwner().getId()));	
		}
		return blog;
	}

	private static class BlogRowMapper implements RowMapper<Blog>{

		@Override
		public Blog mapRow(ResultSet rs, int arg1) throws SQLException {
			// TODO Auto-generated method stub
			int id = rs.getInt("blog_id");
			String name = rs.getString("blog_name");
			String description = rs.getString("blog_description");
			int bloggerId = rs.getInt("blog_ownerId");
			Date created = rs.getTimestamp("blog_created");
			
			Blog blog = new Blog(id);
			blog.setName(name);
			blog.setDescription(description);
			blog.setOwner(new Account(bloggerId));
			blog.setCreated(created);
			return blog;
		}
		
	}

	private static final String SQL_1 = "delete from web_blog where blog_id = ?";
	@Override
	public void deleteBlog(Blog blog) {
		// TODO Auto-generated method stub
		simpleJdbcTemplate.update(SQL_1, blog.getId());
	}

}
